package database

import (
	"context"
	"errors"
	"fmt"
	"strings"

	"github.com/jackc/pgx/v5"
)

type Field struct {
	Name        string
	PgType      string
	Constraints map[ColumnConstraint]struct{}
}

type ColumnConstraint string

const (
	PrimaryKey    ColumnConstraint = "PRIMARY KEY"
	NotNull       ColumnConstraint = "NOT NULL"
	Null          ColumnConstraint = "NULL"
	Unique        ColumnConstraint = "UNIQUE"
	AutoIncrement ColumnConstraint = "AUTO_INCREMENT"
	Index         ColumnConstraint = "INDEX"
)

type TableConstraint interface {
	TableConstraintString() string
}

type ForeignKey struct {
	Columns    []string
	RefTable   string
	RefColumns []string
}

func (fk *ForeignKey) TableConstraintString() string {
	var ColumnsString = strings.Join(fk.Columns, ", ")
	var RefColumnsString = strings.Join(fk.RefColumns, ", ")

	return fmt.Sprintf("FOREIGN KEY (%s) REFERENCES %s (%s)",
		ColumnsString,
		fk.RefTable,
		RefColumnsString,
	)
}

// Create PostgreSQL tables for the schema and children schemas.
func (ts *TableSchema) CreatePgTables(conn *pgx.Conn, createIndices bool) error {
	// create table
	currentSql := fmt.Sprintf(
		// every table should have a primary key, its name being "{TableName}_ID"
		`CREATE TABLE %s (
	%s_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
`,
		ts.TableName(), ts.TableName(),
	)
	// If this is a child table, add another column for the parent ID,
	// which is named as "parent_{ParentTableName}_ID"
	if ts.Parent_schema != nil {
		currentSql += fmt.Sprintf(
			"\tparent_%s_ID INTEGER NOT NULL REFERENCES %s (%s_ID) MATCH FULL,\n",
			ts.Parent_schema.Table_schema.Value().TableName(),
			ts.Parent_schema.Table_schema.Value().TableName(),
			ts.Parent_schema.Table_schema.Value().TableName(),
		)
	}

	switch ts.Kind {
	case Struct:
		var fields []Field
		for field_name, field_attribute := range ts.Attributes {
			fields = append(fields, Field{
				Name:        field_name,
				PgType:      field_attribute.Type.ToPgType(),
				Constraints: field_attribute.Constraints,
			})
		}
		for _, field := range fields {
			currentSql += fmt.Sprintf("\t%s %s", field.Name, field.PgType)
			for constraint := range field.Constraints {
				if constraint != Index {
					currentSql += fmt.Sprintf(" %s", string(constraint))
				} // `INDEX` will be created after tables are created
			}
			currentSql += ",\n"
		}
		currentSql += ");"

		// execute currentSql here
		if _, err := conn.Exec(context.Background(), currentSql); err != nil {
			return err
		}

		// create tables for children
		for _, child_ts := range ts.SubSchemas {
			if err := child_ts.CreatePgTables(conn, createIndices); err != nil {
				return err
			}
		}

		// create index for the table and children
		if createIndices {
			if err := ts.CreatePgIndices(conn, false); err != nil {
				return err
			}
		}
		return nil
	case Slice, Array:
		// The table has three or four columns:
		// - 1. `{table_name}_ID`
		// - 2. `parent_{parent_table_name}_ID`, if ts.Parent_schema != nil,
		//      which is foreign key to the parent table's `{table_name}_ID`
		//      column.
		// - 3. `slice_seq` which represents the index of the children
		//      elements.
		// - 4. `value` if ts.ListType.Table == nil, which means the list
		//      contains basic types;
		//
		// Column 1 & 2 have been created in previous process. Now we are
		// going to create `slice_seq` (& `value`).
		currentSql += "\tslice_seq SMALLINT NOT NULL,\n"
		if ts.ListType.Table == nil {
			// The slice contains only basic data, which will be stored
			// in `value`.
			switch ts.ListType.Kind {
			case Bool:
				currentSql += "\tvalue BOOLEAN\n"
			case Int, Uint:
				currentSql += "\tvalue INTEGER\n"
			case Float:
				currentSql += "\tvalue DOUBLE PRECISION\n"
			case String:
				currentSql += "\tvalue TEXT\n"
			default:
				panic("Impossible!")
			}
			currentSql += ");"
			if _, err := conn.Exec(context.Background(), currentSql); err != nil {
				return err
			}
		} else {
			currentSql += ");"
			// The slice contains a child struct, slice or array.
			// The value of the child object will be got through
			// `parent_{parent_table_name}_ID` column of the child
			// table, which is foreign key to `{table_name}_ID` of
			// this table.
			if _, err := conn.Exec(context.Background(), currentSql); err != nil {
				return err
			}
			// create tables of children TableSchemas
			if err := ts.ListType.Table.CreatePgTables(conn, createIndices); err != nil {
				return err
			}
		}
		// create indices.
		if createIndices {
			// Two indices should be created for the table,
			// One is for (`{table_name}_ID`) (PostgreSQL creates this
			// automatically as it is the primary key), and the other
			// for (`parent_{parent_table_name}_ID`, `slice_seq`) which
			// is named `{table_name}_{"parent_{parent_table_name}_ID"}_idx`
			// if ts.Parent_schema != nil, or otherwise for (`slice_seq`)
			// which is named `{table_name}_slice_seq_idx`.
			if err := ts.CreatePgIndices(conn, false); err != nil {
				return err
			}
		}

		return nil
	default:
		panic("Impossible!")
	}

}

// Create indices for the table of the schema and children schemas.
// Each index is named as {table_name}_{column_name}_idx
func (ts *TableSchema) CreatePgIndices(conn *pgx.Conn, recursive bool) error {
	switch ts.Kind {
	case Struct:
		// create indices for the table
		var sqls []string

		// 1. Create index for foreign key.
		//    PostgreSQL automatically creates an index for the primary key.
		//    However, we need to create indices for foreign keys, who will
		//	  be named as {table_name}_{"parent_{parent_table_name}_ID"}_idx
		if ts.Parent_schema != nil {
			sql := fmt.Sprintf(
				"CREATE INDEX %s_parent_%s_ID_idx ON %s (parent_%s_ID)",
				ts.TableName(),
				ts.Parent_schema.Table_schema.Value().TableName(),
				ts.TableName(),
				ts.Parent_schema.Table_schema.Value().TableName(),
			)
			sqls = append(sqls, sql)
		}

		// 2. Create indices for other columns that have `INDEX` tag.
		for field_name, field_attribute := range ts.Attributes {
			if _, ok := field_attribute.Constraints[Index]; ok {
				sql := fmt.Sprintf(
					"CREATE INDEX %s_%s_idx ON %s (%s)",
					ts.TableName(), field_name, ts.TableName(), field_name,
				)
				sqls = append(sqls, sql)
			}
		}

		// 3. Execute all `CREATE INDEX` commands.
		for _, sql := range sqls {
			if _, err := conn.Exec(context.Background(), sql); err != nil {
				return err
			}
		}

		// 4. Create indices for children tables recursively.
		if recursive {
			for _, child_ts := range ts.SubSchemas {
				if err := child_ts.CreatePgIndices(conn, recursive); err != nil {
					return err
				}
			}
		}

		return nil
	case Slice, Array:
		// The table has three or four columns:
		// - 1. `{table_name}_ID`
		// - 2. `parent_{parent_table_name}_ID`, if ts.Parent_schema != nil,
		//      which is foreign key to the parent table's `{table_name}_ID`
		//      column.
		// - 3. `slice_seq` which represents the index of
		//      the children elements.
		// - 4. `value` if ts.ListType.Table == nil, which means the list
		//      contains basic types;
		//
		// As `{table_name}_ID` is the primary key of the table, we don't
		// need to create an index for it. However, we need to create an
		// index for (`parent_{parent_table_name}_ID`, `slice_seq`) which is
		// named `{table_name}_{"parent_{parent_table_name}_ID"}_idx` if
		// Parent_schema != nil, or otherwise for (`slice_seq`) which is named
		// `{table_name}_slice_seq_idx`.
		var sqls []string
		if ts.Parent_schema != nil {
			sql := fmt.Sprintf(
				"CREATE INDEX %s_parent_%s_ID_idx ON %s (parent_%s_ID, slice_seq)",
				ts.TableName(),
				ts.Parent_schema.Table_schema.Value().TableName(),
				ts.TableName(),
				ts.Parent_schema.Table_schema.Value().TableName(),
			)
			sqls = append(sqls, sql)
		} else {
			sql := fmt.Sprintf(
				"CREATE INDEX %s_slice_seq_idx ON %s (slice_seq)",
				ts.TableName(), ts.TableName(),
			)
			sqls = append(sqls, sql)
		}

		for _, sql := range sqls {
			if _, err := conn.Exec(context.Background(), sql); err != nil {
				return err
			}
		}

		// Recursively create indices for child tables if they exist
		if ts.ListType.Table != nil && recursive {
			if err := ts.ListType.Table.CreatePgIndices(conn, recursive); err != nil {
				return err
			}
		}

		return nil
	default:
		panic("Impossible!")
	}
}

func (ts *TableSchema) RemoveAllTables() error {
	return errors.New("Unimplemented!")
}

func (ts *TableSchema) RemoveAllIndices() error {
	return errors.New("Unimplemented!")
}
